//
//  WSSQLiteManager.swift
//  0920-FMDB
//
//  Created by 武莎莎 on 15/9/21.
//  Copyright © 2015年 itcast. All rights reserved.
//
private let dbName = "readme.db"
class WSSQLiteManager {
 
    static let shareManager = WSSQLiteManager()
    // 全局数据库队列,内有一个串行队列
    var queue:FMDatabaseQueue
    
    // 构造函数中，创建数据库队列(相当于SQLite中打开数据库)
    init() {
        let path = (NSSearchPathForDirectoriesInDomains(NSSearchPathDirectory.DocumentDirectory, NSSearchPathDomainMask.UserDomainMask, true).last! as NSString).stringByAppendingPathComponent(dbName)
        print(path)
        /**
        *创建数据库队列
        *如果数据库存在就会直接打开
        *如果数据库不存在就会新建数据库再打开
        
        *后续所有跟数据库相关的操作，都通过queue来调用
        */
        queue = FMDatabaseQueue(path: path)
        
        createTable()
    }
    // 创建数据表
    func createTable() {
        let path = NSBundle.mainBundle().pathForResource("table.sql", ofType: nil)
        let sql = try! String(contentsOfFile: path!)
    
        queue.inTransaction { (db, rollback) -> Void in
            //executeStatements:执行多条sql语句
            //executeQuery：执行查询
            //executeUpdate：执行删除/更新/插入的操作
            if db.executeStatements(sql) {
                print("创表成功")
            }else {
                print("创表失败")
            }
        }
    }
    
    func insertData() {
        let sql = "INSERT INTO W_Person (name,sex,score) VALUES (?,?,?)"
        queue.inTransaction { (db, rollback) -> Void in
            
            if db.executeUpdate(sql, "zhangsan","male",60) {
                print("更新sql成功")
            }else {
                print("更新sql失败")
            }
            print(db.lastInsertRowId()) // 最后一条插入的行
        }
        
        
    }
    // 查询数据
    func loadData() {
        let sql = "SELECT name,sex,score FROM W_Person;"
        
        queue.inTransaction { (db, rollBack) -> Void in
            // 查询到结果
            guard let result = db.executeQuery(sql) else {
                return
            }
            while result.next() {
                result.stringForColumn("title")
                
                let number = result.intForColumn("number");
                let sex = result.stringForColumn("sex");
                let score = result.doubleForColumn("score");
                
                print("number:\(number),sex:\(sex),score:\(score)")
            }
        }
    }
    // 批量插入数据
    func batchInsert() {
        let start = CFAbsoluteTimeGetCurrent()
        let sql = "INSERT INTO W_Person (name,sex,score) VALUES (?,?,?)"
        queue.inTransaction { (db, rollback) -> Void in
            for i in 0..<10000{
                if !db.executeUpdate(sql, "miaomiao\(i)","female",78) {
                    // 回滚
                    rollback.memory = true
                    return
                }
            }
            print(CFAbsoluteTimeGetCurrent() - start)
        }
    }

}
